﻿using MyBBSWebApi.Core;
using MyBBSWebApi.Models;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace MyBBSWebApi.Dal
{
    public class UserDal
    {
        public List<Users> GetAll()
        {
            List<Users> users =  new List<Users>();
            DataTable dt = SqlHelper.ExecuteTable("select * from Users");
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    Users user = new Users();
                    user.Id = (int)dr["Id"];
                    user.UserNo = dr["UserNo"].ToString();
                    user.UserName = dr["UserName"].ToString();
                    user.UserLevel = (int)dr["UserLevel"];
                    user.Password = dr["Password"].ToString();
                    user.IsDel = (bool)dr["IsDel"];
                    users.Add(user);
                }
            }
            return users;
        }
        public Users GetUserById(int id )
        {
            DataTable dt = SqlHelper.ExecuteTable("select * from Users WHERE Id=@Id",
                new SqlParameter("@Id",id));
            DataRow dr = dt.NewRow();
            if (dt.Rows.Count > 0)
            {
                dr = dt.Rows[0];
            }
            Users user = new Users();
            user.Id = (int)dr["Id"];
            user.UserNo=dr["UserNo"].ToString();
            user.UserName = dr["UserName"].ToString();
            user.UserLevel = (int)dr["UserLevel"];
            user.Password = dr["Password"].ToString();
            return user;
        }

        public List<Users> GetUserByUserNoAndPassword(string userNo,string password)
        {
            DataTable dt = SqlHelper.ExecuteTable("select * from Users WHERE UserNo=@userNo AND Password=@password",
                new SqlParameter("@userNo", userNo),
                new SqlParameter("@password", password));
            List<Users> users = new List<Users>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                users.Add(ToModel(dr));
            }
            return users; 
        }

        public int AddUser(string userNo, string userName, int userLevel, string password)
        {
            return SqlHelper.ExecuteNonQuery($"INSERT INTO Users(UserNo,UserName,UserLevel,Password,IsDel) VALUES(@userNO,@userName,@userLevel,@password,0)",
                new SqlParameter("@userNo", userNo),
                new SqlParameter("@userName", userName),
                new SqlParameter("@userLevel", userLevel),
                new SqlParameter("@password", password));
        }

        public int UpdateUser(int id,string userNo, string userName, int? userLevel, string password)
        {
            DataTable dt = SqlHelper.ExecuteTable("select * from Users WHERE Id=@Id", new SqlParameter("@Id", id));
            int row = 0;
            if (dt.Rows.Count>0)
            {
                DataRow dr = dt.Rows[0];
                Users user = new Users();
                user.Id = (int)dr["Id"];
                user.UserNo = userNo??dr["UserNo"].ToString();
                user.UserName = userName??dr["UserName"].ToString();
                user.UserLevel = userLevel??(int)dr["UserLevel"];
                user.Password = password??dr["Password"].ToString();
                user.IsDel = false;
                row = SqlHelper.ExecuteNonQuery("UPDATE Users SET UserNo=@UserNO,UserName=@UserName,UserLevel=@UserLevel,Password=@Password WHERE Id=@Id",
                new SqlParameter("@Id", user.Id),
                new SqlParameter("@UserNO", user.UserNo),
                new SqlParameter("@UserName", user.UserName),
                new SqlParameter("@UserLevel", user.UserLevel),
                new SqlParameter("@Password", user.Password));
                return row;
            }
            return row;
        }

        public int RemoveUser(int id)
        {
            return SqlHelper.ExecuteNonQuery($"DELETE FROM Users WHERE Id=@Id",
                new SqlParameter("@Id", id));
        }

        private Users ToModel(DataRow dr)
        {
            Users user = new Users();
            user.Id = (int)dr["Id"];
            user.UserNo = dr["UserNo"].ToString();
            user.UserName = dr["UserName"].ToString();
            user.UserLevel = (int)dr["UserLevel"];
            user.Password = dr["Password"].ToString();
            user.IsDel = (bool)dr["IsDel"];
            return user;
        }

        private List<Users> ToModelList(DataTable dt)
        {
            List<Users> users = new List<Users>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                users.Add(ToModel(dr));
            }
            return users;
        }
    }
}
